/***********************************************************************************************************************************
 *    Name: Search4String.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Find the needle in the haystack.
**/
CREATE TABLE #Output (
	table_schema	sysname NOT NULL,
	table_name		sysname NOT NULL,
	column_name		sysname NOT NULL);

DECLARE
	@SearchFor		NVARCHAR(MAX) = N'',
	@tsql_template	NVARCHAR(MAX) = N'IF EXISTS (SELECT * FROM [@table_schema].[@table_name] WHERE [@column_name] LIKE N''%@SearchFor%'') INSERT INTO #Output VALUES (N''@table_schema'', N''@table_name'', N''@column_name'');',
	@table_schema	sysname,
	@table_name		sysname,
	@column_name	sysname,
	@tsql_command	NVARCHAR(MAX);

SET @tsql_template= REPLACE(@tsql_template, N'@SearchFor', @SearchFor);
PRINT @tsql_template;

DECLARE list CURSOR LOCAL FAST_FORWARD FOR
	SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME--, c.DATA_TYPE
	  FROM INFORMATION_SCHEMA.COLUMNS c INNER JOIN INFORMATION_SCHEMA.TABLES t ON (c.TABLE_CATALOG = t.TABLE_CATALOG AND c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME)
	  WHERE TABLE_TYPE = N'BASE TABLE'
		AND DATA_TYPE LIKE N'%char' OR DATA_TYPE LIKE N'%text';
OPEN list;
  WHILE (0=0) BEGIN;
	FETCH NEXT FROM list INTO @table_schema, @table_name ,@column_name;
	IF @@FETCH_STATUS <> 0 BREAK;
	SET @tsql_command= @tsql_template;
	SET @tsql_command= REPLACE(@tsql_command, N'@table_schema', @table_schema);
	SET @tsql_command= REPLACE(@tsql_command, N'@table_name'  , @table_name);
	SET @tsql_command= REPLACE(@tsql_command, N'@column_name' , @column_name);
	EXECUTE (@tsql_command);
  END;
CLOSE list;
DEALLOCATE list;
GO
SELECT * FROM #Output;